package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import logic.Product;
import logic.User;

public class JDBCFinderDAO implements IFinderDAO {

	public List<Product> lastProductsFind(Integer n) {
		Connection conn = ConnectionManager.getInstance().checkOut();
        List<Product> searchResults = new LinkedList<Product>();

        JDBCProductDAO pdao = new JDBCProductDAO();
    	//Selecciona los ultimos productos a�adidos al catalogo
        searchResults = pdao.selectLastProduct(conn, n);
        return searchResults;
	}
	
	public List<List<Product>> defaultFind(User u) {
		Connection conn = ConnectionManager.getInstance().checkOut();
        List<List<Product>> searchResults = new LinkedList<List<Product>>();
        List<Product> lp = new LinkedList<Product>();

        JDBCProductDAO pdao = new JDBCProductDAO();
    	//Selecciona los productos comprados por el usuario
    	//agrupados y ordenados por categorias
        lp = pdao.selectSellProducts(conn, u);
        for(Product p: lp){
        	searchResults.add(pdao.selectProductsByCategory(conn, p.getCategory()));
        }
        String destiny = pdao.mostDestinyProfitsByUser(conn, u);
        searchResults.add(pdao.selectProductByDestination(conn, destiny));
        return searchResults;
	}

	public List<Product> newFind(List<String> l) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;
        
        String sql = "SELECT * FROM products WHERE active = true AND sell = false ";
        if(l.get(0) != null && l.get(0) != ""){
        	sql += " AND name LIKE '%"+l.get(0)+"%' ";
        }
        if(!l.get(1).equals("0")){
        	sql += " AND category = ? ";
        }
        if(!l.get(2).equals("0")){
        	sql += " AND iddestination = ? ";
        }

        try {
            stmt = conn.prepareStatement(sql);
            boolean b = false;
            if(!l.get(1).equals("0")){
            	stmt.setString(1, l.get(1));
            	b = true;
            }
            if(!l.get(2).equals("0")){
            	if(b){
            		stmt.setString(2, l.get(2));
            	}else{
            		stmt.setString(1, l.get(2));
            	}
            }
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                System.out.print(temp.getName());
                temp.setId(result.getString("id"));
                temp.setCategory(result.getString("category"));
                temp.setName(result.getString("name"));
                temp.setDescription(result.getString("description"));
                temp.setPrice(result.getFloat("price"));
                temp.setIddonor(result.getString("iddonor"));
                temp.setIdbuyer(result.getString("idbuyer"));
                temp.setIddestination(result.getString("iddestination"));
                temp.setRegdate(result.getString("regdate"));
                temp.setActive(result.getBoolean("active"));
                temp.setSell(result.getBoolean("sell"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
	}
	
	public List<Product> randomFind() {
		Connection conn = ConnectionManager.getInstance().checkOut();
        List<Product> searchResults = new LinkedList<Product>();

        JDBCProductDAO pdao = new JDBCProductDAO();
    	//Selecciona los ultimos productos a�adidos al catalogo
        searchResults = pdao.selectRandomProduct(conn, 20);
        return searchResults;
	}
	
	public List<String> getCategories() {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        List<String> searchResults = new LinkedList<String>();
        ResultSet result = null;

        try {
            String sql = "SELECT * FROM categories";
            stmt = conn.prepareStatement(sql);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                searchResults.add(result.getString("category"));
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;

        
	}

}